using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Database data database (state)
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Creates new State.
        /// </summary>
        /// <param name="state">The specific state.</param>
        public override void CreateState(State state)
        {
            String table = state.Type.ToString().ToUpper() + "_STATE";
            DbParameter dbTable = CreateParameter("tableName", table);
            DbParameter dbState = CreateParameter("name", state.Name);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("state/CreateState", dbTable, dbState);
                if (data != 1)
                {
                    throw new Exception("State " + state.Name + " NOT created!");
                }
                connection.Close();
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Updates the State.
        /// </summary>
        /// <param name="state">The specific state.</param>
        public override void UpdateState(State state)
        {
            String table = state.Type.ToString().ToUpper() + "_STATE";
            DbParameter dbTable = CreateParameter("tableName", table);
            DbParameter dbState = CreateParameter("name", state.Name);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("state/UpdateState", dbTable, dbState);
                if (data != 1)
                {
                    throw new Exception("State " + state.Name + " NOT updated!");
                }
                connection.Close();
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Deletes the state.
        /// </summary>
        /// <param name="state">The specific state.</param>
        public override void DeleteState(State state)
        {
            String table = state.Type.ToString().ToUpper() + "_STATE";
            DbParameter dbTable = CreateParameter("tableName", table);
            DbParameter dbId = CreateParameter("id", state.ID);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("state/CreateState", dbTable, dbId);
                if (data != 1)
                {
                    throw new Exception("State " + state.Name + " NOT deleted!");
                }
                connection.Close();
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }



        /// <summary>
        /// Get all states of specific type.
        /// </summary>
        /// <param name="type">Type of state to return</param>
        public override IList<State> GetAllStates(State.StateTypes type)
        {
            //String table = "SELECT ID, STATE_NAME FROM " +
            //    type.ToString().ToUpper() + "_STATE";
            String table = String.Format("SELECT ID, NAME FROM {0}_STATE",type.ToString().ToUpper());
            IList<State> result = new List<State>();
            try
            {
                connection.Open();
                DbDataReader data = GetGridByQuery(table);

                while (data.Read())
                {
                    State state = new State(
                        GetIntByColumn(data, "ID")
                        , GetStringByColumn(data, "NAME")
                        , type);
                    result.Add(state);
                }

                connection.Close();
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            return result;
        }


        /// <summary>
        /// Get all states of specific type.
        /// </summary>
        /// <param name="type">Type of state to return</param>
        /// <param name="stateId">id of state</param>
        public override State GetStateForType(int stateId, State.StateTypes type)
        {
            string stateName = type.ToString().ToUpper() + "_STATE";
            //String table = "SELECT ID, STATE_NAME FROM " +
            //    type.ToString().ToUpper() + "_STATE WHERE ID = '" + stateId + "'";
            String table = String.Format("SELECT ID, NAME FROM {0} WHERE ID = '{1}'", stateName, stateId);

            State state = null;
            try
            {
                connection.Open();
                DbDataReader data = GetGridByQuery(table);
                data.Read();
                state = new State(GetIntByColumn(data, "ID")
                    , GetStringByColumn(data, "NAME")
                    , type);
                connection.Close();
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            return state;
        }
    }
}
